import json

import requests
import time
import pymysql
import xlrd
import xlwt

from Db_interface import DB_interface

db = DB_interface().online_db()  # 线上数据库
cursor = db.cursor()


def read_excel_new(all_item, paid_date):
    item_li = []
    data = xlrd.open_workbook('官费缴纳汇总表.xls')
    sheet_names = data.sheet_names()
    for sheet_name in sheet_names[:1]:
        table = data.sheet_by_name(sheet_name)
        for row in range(table.nrows):
            item = {}
            # 序号	申请号/专利号/国际申请号/海牙转交编号	业务类型	票据抬头	统一社会信用代码	费用种类	外币金额	费用金额（人民币）	备注
            row_datas = table.row_values(row)
            if '序号' not in row_datas:
                item['serial_number'] = str(int(row_datas[0]))
                item['application_number'] = row_datas[1]
                item['money_type'] = row_datas[5]
                item['money_number'] = row_datas[7]
                item['close_date'] = ''
                now_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
                item['update_date'] = now_time  # 例如：'2020-12-24 17:00:00'
                item['paid_date'] = paid_date
                item_li.append(item)
    all_item['item_li'] = item_li
    return all_item


def upload_to_db(item):
    sql = "insert into ccy_paid (serial_number,application_number,money_type,money_number,close_date,update_date,paid_date) values('{}','{}','{}','{}','{}','{}','{}')".format(
        item['serial_number'], item['application_number'], item['money_type'], item['money_number'], item['close_date'],
        item['update_date'], item['paid_date'])
    cursor.execute(sql)
    db.commit()


def create_all_item(item, all_item):
    money_str = item['money_type'] + str(int(float(item['money_number'])))
    if item['application_number'] in all_item.keys():
        all_item[item['application_number']]['money_li'].append(money_str)
        all_item[item['application_number']]['money_str'] = '+'.join(all_item[item['application_number']]['money_li'])
        all_item[item['application_number']]['money_num'] += int(float(item['money_number']))
    else:
        sqh_detail = {}
        sqh_detail['money_li'] = []
        sqh_detail['money_li'].append(money_str)
        sqh_detail['money_str'] = money_str
        sqh_detail['money_num'] = int(float(item['money_number']))
        all_item[item['application_number']] = sqh_detail
    return all_item


def get_paid_sqh(paid_date):
    sql = f"select application_number from ccy_paid where paid_date='{paid_date}'"
    cursor.execute(sql)
    content = cursor.fetchall()
    paid_sqh_li = []
    for i in content:
        paid_sqh_li.append(i[0])
    return list(set(paid_sqh_li))


def get_paid_detail(sqh, paid_date):
    sql = f"select * from ccy_paid where application_number='{sqh}' and paid_date= '{paid_date}' and money_type NOT LIKE '%滞纳金%'"
    cursor.execute(sql)
    content = cursor.fetchall()
    return content


def parse_paid_detail(content):
    money_type_li = []
    for i in content:
        money_str = i[3] + str(int(float(i[4])))
        money_type_li.append(money_str)
    money_type = '+'.join(money_type_li)
    type = ''
    if '年费' in money_type:
        type = '2'
    elif '滞纳金' in money_type:
        type = '2'
    elif '著录事项变更费' in money_type:
        type = '3'
    elif '申请费' in money_type:
        type = '1'
    elif '优先权' in money_type:
        type = '1'
    elif '专利文件副本证明费' in money_type:
        type = '1'
    elif '公布印刷费' in money_type:
        type = '1'
    elif '实质审查费' in money_type:
        type = '1'
    else:
        print('特殊情况')
        if '恢复权利请求费' in money_type:
            type = '2'
    return type, money_type


def get_paid_date():
    sql = f"select paid_date from ccy_paid group by paid_date"
    cursor.execute(sql)
    content = cursor.fetchall()
    paid_date_li = []
    for i in content:
        paid_date_li.append(i[0])
    return paid_date_li


def get_login():
    headers = {"Accept": "application/json", }
    url = 'https://api.xinhehonggu.com/api/auth/login'
    phone = '18866668888'
    password = 'xhhg2019'
    source = 'admin'
    data = {
        'method': '1',
        'phone': phone,
        'password': password,
        'source': source,
    }
    r = requests.post(url=url, data=data, headers=headers, verify=False)
    res_dict = r.json()
    token = res_dict['data']['token']
    return token


def update_xhhg_system(item, token, update_li):
    headers = {"Accept": "application/json", "authorization": "Bearer " + token}
    # url = f"http://test.api.whxhhgip.com/api/admin/financialManagement/payRecord/paySuccess/country"
    url = f"http://api.xinhehonggu.com/api/admin/financialManagement/payRecord/paySuccess/country/"
    num = 0
    if item['type'] == "2":         # type = "2" 为传年费参数
        num_type = item['money_type'].split('+')
        sss = [i for i in num_type if "年年费" in i][0]
        item['num'] = sss.split("年年费")[0] + '年年费'
    print("data: ", item)
    json_dict = update_retry(num, url, headers, item)
    new_item = {}
    new_item['sqh'] = item['applicationNumber']
    new_item['type'] = item['type']
    new_item['status'] = json_dict['status']
    new_item['message'] = json_dict['message'] if 'message' in json_dict.keys() else ''
    new_item['money_type'] = item['money_type']
    update_li.append(new_item)


def update_retry(num, url, headers, item):
    if num < 10:
        try:
            num += 1
            response = requests.post(url, headers=headers, data=item)

            print('response:', response.json())
            return json.loads(response.content.decode())
        except Exception as e:
            print('重试第{}次,{}'.format(num, e))
            time.sleep(2)
            update_retry(num, url, headers, item)


def get_type_item(all_item, paid_date):
    paid_sqh_li = get_paid_sqh(paid_date)
    type_item_li = []
    for sqh in paid_sqh_li:
        type_item = {}
        content = get_paid_detail(sqh, paid_date)
        type, money_type = parse_paid_detail(content)
        type_item['type'] = type
        type_item['applicationNumber'] = 'ZL' + sqh
        type_item['money_type'] = money_type
        type_item_li.append(type_item)
    all_item['type_item_li'] = type_item_li
    return all_item


def update_xhhg_api(all_item, paid_date):
    print('开始给系统接口更新, 倒计时3秒')
    token = get_login()
    update_li = []
    for type_item in all_item['type_item_li']:
        type_item['pay_country_at'] = paid_date
        print("type_item:", type_item)
        update_xhhg_system(type_item, token, update_li)
    save_json_dict(update_li, paid_date)


def save_json_dict(update_li, paid_date):
    print("update_li: ", update_li)
    print("paid_date: ", paid_date)
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("sheet1")
    sheet.write(0, 0, '序号')
    sheet.write(0, 1, '申请号')
    sheet.write(0, 2, '类型数字')
    sheet.write(0, 3, '是否成功')
    sheet.write(0, 4, '信息')
    sheet.write(0, 5, '费用')
    if len(update_li) > 0:
        for i in range(len(update_li)):
            sheet.write(i + 1, 0, i + 1)
            sheet.write(i + 1, 1, update_li[i]['sqh'])
            sheet.write(i + 1, 2, update_li[i]['type'])
            sheet.write(i + 1, 3, update_li[i]['status'])
            sheet.write(i + 1, 4, update_li[i]['message'])
            sheet.write(i + 1, 5, update_li[i]['money_type'])
        workbook.save(f'./save_fee_dir/{paid_date}更新系统缴费状态.xls')


def juage_paid_date(paid_date):
    paid_date_li = get_paid_date()
    if paid_date in paid_date_li:
        print('请核对缴费日期是否正确')
        return False
    return True


def update_db(all_item):
    print('===================================开始插入数据库===================================')
    for item in all_item['item_li']:
        upload_to_db(item)  # 插入数据库
    print('插入数据库 ccy_paid 成功!')
 

def run():
    all_item = {}
    paid_date = '2024-08-01'  # 日期更改为当天
    flag = juage_paid_date(paid_date)  # 检查数据库中是否有该条日期
    if flag:
        all_item = read_excel_new(all_item, paid_date)
        update_db(all_item)  # 入库
        all_item = get_type_item(all_item, paid_date)
        update_xhhg_api(all_item, paid_date)


if __name__ == '__main__':
    run()
